const connection = require('../app/database')
const {timeFormat} = require('../utils/format')
class SportsService {
  async createSports(name,time,time2) {
    const statement = `
      INSERT INTO sports(sport_name,sport_time) VALUES (?,?);
    `
    const [res] = await connection.execute(statement,[name,time])
    return res
  }
  async getAllSports(pagenum = '1', pagesize = '10', id = '', name = '', time = []) {
    let startTime
    let endTime
    let a = ''
    if (time.length === 2) {
      startTime = timeFormat(time[0])
      endTime = timeFormat(time[1])
      a = 'AND ((sport_time >= "'+startTime+'") AND (sport_time <= "'+endTime+'"))'
    }
    console.log(startTime);
    console.log(endTime);

    const statement = `		 	
      SELECT (SELECT COUNT(*) FROM 
      (SELECT * FROM sports
      WHERE sport_id LIKE '%${id}%' AND sport_name LIKE '%${name}%' ${a}
      LIMIT 0,10) a) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',s.sport_id,'name',s.sport_name,'time',s.sport_time)) list
      FROM	(SELECT * FROM sports
      WHERE sport_id LIKE '%${id}%' AND sport_name LIKE '%${name}%' ${a}
      LIMIT ?,?) s;
    `

    const offset = (pagenum - 1) * pagesize
    const [result] = await connection.execute(statement, [offset+'', pagesize])
    return result[0]
   
  }
  async getSportsById(sport_id) {
    const statement = `
      SELECT * FROM sports WHERE sport_id = ?;
    `
    const [res] = await connection.execute(statement,[sport_id])
    return res
  }
  async removeSportsById(id) {
    const statement = `
      DELETE FROM sports WHERE sport_id = ?;
    `
    const [res] = await connection.execute(statement,[id])
    return res
  }
  async updateSportsById(id, name, time) {
    const statement = `
      UPDATE sports SET sport_name = ?,sport_time = ?
      WHERE sport_id = ?;
    `
    const [res] = await connection.execute(statement,[name,time,id])
    return res
  }
}

module.exports = new SportsService()